from openpyxl import load_workbook
import time
from tqdm import tqdm


def aa(lookup_value):
    for x in range(2, ws.max_row + 1):
        if ws.cell(x, 1).value == lookup_value:
            return x


t = time.localtime()
date_today = time.strftime("%Y.%m.%d", t)

wb_1 = load_workbook(r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\4，原材料安全库存\2024.01.22\出库信息2024.01.22.xlsx")
ws_1 = wb_1.active

wb = load_workbook("//192.168.70.101/19计划管理部/01.计划/5，物控管理/4，原材料安全库存/2024.01.22/物料使用量分析2024.01.22.xlsx")
ws = wb["月度使用量"]

for i in tqdm(range(2, ws_1.max_row + 1)):
    for j in range(6, 30):
        if ws.cell(1, j).value == ws_1.cell(i, 2).value:
            y = aa(ws_1.cell(i, 1).value)
            ws.cell(y, j).value += ws_1.cell(i, 3).value
        continue


for i in tqdm(range(2, ws.max_row + 1)):
    a = 0
    b = 0
    for j in range(29, 5, -1):
        if ws.cell(i, j).value != 0:
            a = 29 - j
            b += 1
    ws.cell(i, 4).value = b
    ws.cell(i, 5).value = a

wb.save("//192.168.70.101/19计划管理部/01.计划/5，物控管理/4，安全库存管理/2023.12.14/物料使用量整理.xlsx")
